package wstrader.task.trade.reports;

import pc.system.*;

import wstrader.task.GenReport;
import wstrader.task.trade.TradeTask;

import java.sql.ResultSet;
import java.sql.SQLException;
import pc.util.*;

public class Position extends TradeTask
{
  public Position(Tasks owner)
  {
    super(owner,"position");
  }
  
  public long doTask()
  {
  	long Ret =0;
    int task_id = this.getRequest().getIntValue("task_id");
    switch (task_id)
	{
    	case 5: //Report
    		getResponse().setReport(new PrintPositionReport(this).generate());
    		break;  
    		
        	default : //View only
            break;	
	}
  	
  	return Ret;
  }
  
  public float getSumUnfixStkPositionReport(String Mrk)
  {
    float val = 0;
    String sql = "SELECT sum(receive_unfix_tons) as name FROM contracts where terminal_market = '" + Mrk + "' and contract_price_type = 'U' and type = 'P'";
    ResultSet rs = getSQLCommand().executeQuery(sql);

    try {
    	rs.beforeFirst();
	  	if (rs.next()==true)
	  	{
	  		val = rs.getFloat("name");
	  	}
	  	else
	  	{
	  		val=0;
	  		return val;
	  	}
      rs.close();
    } catch (SQLException e) {
      System.out.println(e.getMessage());
      val =0;
      return val;
    }
    return val;
  }	  
  
  
  public float getSumfixStkPositionReport(int Q_id,String Mrk)
  {
    float val = 0;
    float RecdTons = 0;
    float InvTons = 0;
    float wip =0;
    float wop =0;
    
    RecdTons = getTotStockTon(Q_id,0);
    
    InvTons = getTotInvTon(Q_id,0);
    
	float uf_stk = getSumUnfixStkPositionReport(Mrk);
	
	wip = this.getTotInProcess(Q_id,0);
	wop = this.getTotOutProcess(Q_id,0);
	
	val = (RecdTons-InvTons)-uf_stk + (wip-wop);

    return val;
  }	

 public float getBrokerPosition(java.sql.Date TDt,String TM,int BkId,String Eq)
 {
 	float TMTon=0;
 	String StrSql = "SELECT sum(futures_contract.tons) as Tons " +
			 "FROM futures_contract " + 
			 "where terminal_market = '" + TM + "' " +
			 "and terminal_month " + Eq + " '" + TDt + "' " +
			 "and broker_id = " + BkId;// +
			 //" group by terminal_market,broker_id,terminal_month";
 	
 	ResultSet Rs = getSQLCommand().executeQuery(StrSql);
 	try {
		if (Rs.next()==true) 
		{
			TMTon = Rs.getFloat("Tons");
		}
		else
		{
			TMTon=0;
		}
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
 	return TMTon;
 }
  
 public ResultSet GetBrokerList()
 {
 	return getSQLCommand().executeQuery("select id,name from futures_account_master");
 }
 
}

class PrintPositionReport extends GenReport
{
	  private Position position = null;
	  public PrintPositionReport(Tasks owner)
	  {
	    super(owner,"PositionReport.xls");
	    this.position = (Position)owner;
	  }
	  
	  public String generate()
	  {
	  	writePositionReport("NYC",5);
	  	writePositionReport("LIFFE",26);
	  	return export();
	  }
	  
	  private void writePositionReport(String Mrk,int Row)
	  {
		int dloop=1;
		float ufp_Month1 = 0;
		float ufp_Month2 = 0;
		float ufp_Month3 = 0;
		float ufp_Month4 = 0;
		float ufp_Month5 = 0;
		float ufp_Month6 = 0;
		
		float uf_stk = 0;
		
		float ufs_Month1 = 0;
		float ufs_Month2 = 0;
		float ufs_Month3 = 0;
		float ufs_Month4 = 0;
		float ufs_Month5 = 0;
		float ufs_Month6 = 0;

		float uftbf_Month1 = 0;
		float uftbf_Month2 = 0;
		float uftbf_Month3 = 0;
		float uftbf_Month4 = 0;
		float uftbf_Month5 = 0;
		float uftbf_Month6 = 0;
		
		float fp_Month1 = 0;
		float fp_Month2 = 0;
		float fp_Month3 = 0;
		float fp_Month4 = 0;
		float fp_Month5 = 0;
		float fp_Month6 = 0;
		
		float f_stk=0;
		
		float fs_Month1 = 0;
		float fs_Month2 = 0;
		float fs_Month3 = 0;
		float fs_Month4 = 0;
		float fs_Month5 = 0;
		float fs_Month6 = 0;
		
		float npr_Month1 = 0;
		float npr_Month2 = 0;
		float npr_Month3 = 0;
		float npr_Month4 = 0;
		float npr_Month5 = 0;
		float npr_Month6 = 0;
		
		float fup_Month1 = 0;
		float fup_Month2 = 0;
		float fup_Month3 = 0;
		float fup_Month4 = 0;
		float fup_Month5 = 0;
		float fup_Month6 = 0;
		
		int q_id =0;
		
		if (Mrk.compareTo("LIFFE")==0) q_id = 2;
		if (Mrk.compareTo("NYC")==0) q_id = 1;
		
		ResultSet rs   = position.GetTerminalMthList(Mrk);
		try {
			rs.beforeFirst();
		
		while (rs.next())
		{
			if (dloop==1) ufp_Month1 = position.getSumPositionReport("pending_unfix_tons","terminal_month","terminal_market","contracts",rs.getDate("month"),Mrk,"<=","P");
			if (dloop==2) ufp_Month2 = position.getSumPositionReport("pending_unfix_tons","terminal_month","terminal_market","contracts",rs.getDate("month"),Mrk,"=","P");
			if (dloop==3) ufp_Month3 = position.getSumPositionReport("pending_unfix_tons","terminal_month","terminal_market","contracts",rs.getDate("month"),Mrk,"=","P");
			if (dloop==4) ufp_Month4 = position.getSumPositionReport("pending_unfix_tons","terminal_month","terminal_market","contracts",rs.getDate("month"),Mrk,"=","P");
			if (dloop==5) ufp_Month5 = position.getSumPositionReport("pending_unfix_tons","terminal_month","terminal_market","contracts",rs.getDate("month"),Mrk,"=","P");
			if (dloop==6) ufp_Month6 = position.getSumPositionReport("pending_unfix_tons","terminal_month","terminal_market","contracts",rs.getDate("month"),Mrk,">=","P");
			
			if (dloop==1)  uf_stk = position.getSumUnfixStkPositionReport(Mrk);
			
			if (dloop==1) ufs_Month1 = position.getSumPositionReport("unfixed_tons","terminal_month","terminal_market","contracts",rs.getDate("month"),Mrk,"<=","S");
			if (dloop==2) ufs_Month2 = position.getSumPositionReport("unfixed_tons","terminal_month","terminal_market","contracts",rs.getDate("month"),Mrk,"=","S");
			if (dloop==3) ufs_Month3 = position.getSumPositionReport("unfixed_tons","terminal_month","terminal_market","contracts",rs.getDate("month"),Mrk,"=","S");
			if (dloop==4) ufs_Month4 = position.getSumPositionReport("unfixed_tons","terminal_month","terminal_market","contracts",rs.getDate("month"),Mrk,"=","S");
			if (dloop==5) ufs_Month5 = position.getSumPositionReport("unfixed_tons","terminal_month","terminal_market","contracts",rs.getDate("month"),Mrk,"=","S");
			if (dloop==6) ufs_Month6 = position.getSumPositionReport("unfixed_tons","terminal_month","terminal_market","contracts",rs.getDate("month"),Mrk,">=","S");

			if (dloop==1) uftbf_Month1 = (uf_stk + ufp_Month1) - ufs_Month1;
			if (dloop==2) uftbf_Month2 = ufp_Month2 - ufs_Month2;
			if (dloop==3) uftbf_Month3 = ufp_Month3 - ufs_Month3;
			if (dloop==4) uftbf_Month4 = ufp_Month4 - ufs_Month4;
			if (dloop==5) uftbf_Month5 = ufp_Month5 - ufs_Month5;
			if (dloop==6) uftbf_Month6 = ufp_Month6 - ufs_Month6;
			
			if (dloop==1) fp_Month1 = position.getSumPositionReport("pending_fix_tons","terminal_month","terminal_market","contracts",rs.getDate("month"),Mrk,"<=","P");
			if (dloop==2) fp_Month2 = position.getSumPositionReport("pending_fix_tons","terminal_month","terminal_market","contracts",rs.getDate("month"),Mrk,"=","P");
			if (dloop==3) fp_Month3 = position.getSumPositionReport("pending_fix_tons","terminal_month","terminal_market","contracts",rs.getDate("month"),Mrk,"=","P");
			if (dloop==4) fp_Month4 = position.getSumPositionReport("pending_fix_tons","terminal_month","terminal_market","contracts",rs.getDate("month"),Mrk,"=","P");
			if (dloop==5) fp_Month5 = position.getSumPositionReport("pending_fix_tons","terminal_month","terminal_market","contracts",rs.getDate("month"),Mrk,"=","P");
			if (dloop==6) fp_Month6 = position.getSumPositionReport("pending_fix_tons","terminal_month","terminal_market","contracts",rs.getDate("month"),Mrk,">=","P");
			
			if (Mrk.compareTo("NYC")==0)
			{
				if (dloop==1) f_stk = position.getSumfixStkPositionReport(q_id,"NYC");
			}
			if (Mrk.compareTo("LIFFE")==0)
			{
				if (dloop==1) f_stk = position.getSumfixStkPositionReport(q_id,"LIFFE");
			}
			
			
			
			if (dloop==1) fs_Month1 = position.getSumPositionReport("fixed_tons-inv_tons","terminal_month","terminal_market","contracts",rs.getDate("month"),Mrk,"<=","S");
			if (dloop==2) fs_Month2 = position.getSumPositionReport("fixed_tons-inv_tons","terminal_month","terminal_market","contracts",rs.getDate("month"),Mrk,"=","S");
			if (dloop==3) fs_Month3 = position.getSumPositionReport("fixed_tons-inv_tons","terminal_month","terminal_market","contracts",rs.getDate("month"),Mrk,"=","S");
			if (dloop==4) fs_Month4 = position.getSumPositionReport("fixed_tons-inv_tons","terminal_month","terminal_market","contracts",rs.getDate("month"),Mrk,"=","S");
			if (dloop==5) fs_Month5 = position.getSumPositionReport("fixed_tons-inv_tons","terminal_month","terminal_market","contracts",rs.getDate("month"),Mrk,"=","S");
			if (dloop==6) fs_Month6 = position.getSumPositionReport("fixed_tons-inv_tons","terminal_month","terminal_market","contracts",rs.getDate("month"),Mrk,">=","S");
			
			if (dloop==1) npr_Month1 = (f_stk+fp_Month1) - fs_Month1;
			if (dloop==2) npr_Month2 = fp_Month2 - fs_Month2;
			if (dloop==3) npr_Month3 = fp_Month3 - fs_Month3;
			if (dloop==4) npr_Month4 = fp_Month4 - fs_Month4;
			if (dloop==5) npr_Month5 = fp_Month5 - fs_Month5;
			if (dloop==6) npr_Month6 = fp_Month6 - fs_Month6;
		
			if (dloop==1) fup_Month1 = position.getSumPositionReport("tons","terminal_month","terminal_market","futures_contract",rs.getDate("month"),Mrk,"<=","");
			if (dloop==2) fup_Month2 = position.getSumPositionReport("tons","terminal_month","terminal_market","futures_contract",rs.getDate("month"),Mrk,"=","");
			if (dloop==3) fup_Month3 = position.getSumPositionReport("tons","terminal_month","terminal_market","futures_contract",rs.getDate("month"),Mrk,"=","");
			if (dloop==4) fup_Month4 = position.getSumPositionReport("tons","terminal_month","terminal_market","futures_contract",rs.getDate("month"),Mrk,"=","");
			if (dloop==5) fup_Month5 = position.getSumPositionReport("tons","terminal_month","terminal_market","futures_contract",rs.getDate("month"),Mrk,"=","");
			if (dloop==6) fup_Month6 = position.getSumPositionReport("tons","terminal_month","terminal_market","futures_contract",rs.getDate("month"),Mrk,">=","");
		
			dloop=dloop+1;
		}
		
		rs.first();
		
        this.setCellValue(5,Row,Utils.dateToStr(rs.getDate("month")).substring(3));
		rs.next();
		this.setCellValue(6,Row,Utils.dateToStr(rs.getDate("month")).substring(3));
		rs.next();
		this.setCellValue(7,Row,Utils.dateToStr(rs.getDate("month")).substring(3));
		rs.next();
		this.setCellValue(8,Row,Utils.dateToStr(rs.getDate("month")).substring(3));
		rs.next();
		this.setCellValue(9,Row,Utils.dateToStr(rs.getDate("month")).substring(3));
		if (Mrk.compareTo("LIFFE")==0)
		{
			rs.next();
			this.setCellValue(10,Row,Utils.dateToStr(rs.getDate("month")).substring(3));
		}
		
		Row=Row+4;
		this.setCellValue(5,Row,ufp_Month1);
		this.setCellValue(6,Row,ufp_Month2);
		this.setCellValue(7,Row,ufp_Month3);
		this.setCellValue(8,Row,ufp_Month4);
		this.setCellValue(9,Row,ufp_Month5);
		if (Mrk.compareTo("LIFFE")==0)
		{
			this.setCellValue(10,Row,ufp_Month6);
		}
		this.getCell(11,Row).setCellFormula("SUM(F" + (Row+1) + ":K" + (Row+1) +")");
		
		Row=Row+1;
		this.setCellValue(5,Row,uf_stk);		
		this.getCell(11,Row).setCellFormula("SUM(F" + (Row+1) + ":K" + (Row+1) +")");
		
		Row=Row+1;
		this.setCellValue(5,Row,ufs_Month1);
		this.setCellValue(6,Row,ufs_Month2);
		this.setCellValue(7,Row,ufs_Month3);
		this.setCellValue(8,Row,ufs_Month4);
		this.setCellValue(9,Row,ufs_Month5);
		if (Mrk.compareTo("LIFFE")==0)
		{
			this.setCellValue(10,Row,ufs_Month6);
		}
		this.getCell(11,Row).setCellFormula("SUM(F" + (Row+1) + ":K" + (Row+1) +")");
		
		Row=Row+1;
		this.setCellValue(5,Row,uftbf_Month1);
		this.setCellValue(6,Row,uftbf_Month2);
		this.setCellValue(7,Row,uftbf_Month3);
		this.setCellValue(8,Row,uftbf_Month4);
		this.setCellValue(9,Row,uftbf_Month5);
		if (Mrk.compareTo("LIFFE")==0)
		{
			this.setCellValue(10,Row,uftbf_Month6);
		}
		this.getCell(11,Row).setCellFormula("SUM(F" + (Row+1) + ":K" + (Row+1) +")");
		
		Row=Row+2;
		this.setCellValue(5,Row,fp_Month1);
		this.setCellValue(6,Row,fp_Month2);
		this.setCellValue(7,Row,fp_Month3);
		this.setCellValue(8,Row,fp_Month4);
		this.setCellValue(9,Row,fp_Month5);
		if (Mrk.compareTo("LIFFE")==0)
		{
			this.setCellValue(10,Row,fp_Month6);
		}
		this.getCell(11,Row).setCellFormula("SUM(F" + (Row+1) + ":K" + (Row+1) +")");
		
		Row=Row+1;
		this.setCellValue(5,Row,f_stk);		
		this.getCell(11,Row).setCellFormula("SUM(F" + (Row+1) + ":K" + (Row+1) +")");
		
		Row=Row+1;
		this.setCellValue(5,Row,fs_Month1);
		this.setCellValue(6,Row,fs_Month2);
		this.setCellValue(7,Row,fs_Month3);
		this.setCellValue(8,Row,fs_Month4);
		this.setCellValue(9,Row,fs_Month5);
		if (Mrk.compareTo("LIFFE")==0)
		{
			this.setCellValue(10,Row,fs_Month6);
		}
		this.getCell(11,Row).setCellFormula("SUM(F" + (Row+1) + ":K" + (Row+1) +")");
		
		Row=Row+1;
		this.setCellValue(5,Row,npr_Month1);
		this.setCellValue(6,Row,npr_Month2);
		this.setCellValue(7,Row,npr_Month3);
		this.setCellValue(8,Row,npr_Month4);
		this.setCellValue(9,Row,npr_Month5);
		if (Mrk.compareTo("LIFFE")==0)
		{
			this.setCellValue(10,Row,npr_Month6);
		}
		this.getCell(11,Row).setCellFormula("SUM(F" + (Row+1) + ":K" + (Row+1) +")");
		
		Row=Row+2;
		if (Mrk.compareTo("NYC")==0)
		{
			this.getCell(5,Row).setCellFormula("SUM(F13,F18)");
			this.getCell(6,Row).setCellFormula("SUM(G13,G18)");
			this.getCell(7,Row).setCellFormula("SUM(H13,H18)");
			this.getCell(8,Row).setCellFormula("SUM(I13,I18)");
			this.getCell(9,Row).setCellFormula("SUM(J13,IJ8)");		
		}
		if (Mrk.compareTo("LIFFE")==0)
		{
			this.getCell(5,Row).setCellFormula("SUM(F34,F39)");
			this.getCell(6,Row).setCellFormula("SUM(G34,G39)");
			this.getCell(7,Row).setCellFormula("SUM(H34,H39)");
			this.getCell(8,Row).setCellFormula("SUM(I34,I39)");
			this.getCell(9,Row).setCellFormula("SUM(J34,J39)");			
		}
		this.getCell(11,Row).setCellFormula("SUM(F" + (Row+1) + ":K" + (Row+1) +")");
		
		Row=Row+2;
		this.setCellValue(5,Row,fup_Month1);
		this.setCellValue(6,Row,fup_Month2);
		this.setCellValue(7,Row,fup_Month3);
		this.setCellValue(8,Row,fup_Month4);
		this.setCellValue(9,Row,fup_Month5);
		if (Mrk.compareTo("LIFFE")==0)
		{
			this.setCellValue(10,Row,fup_Month6);
		}		
		this.getCell(11,Row).setCellFormula("SUM(F" + (Row+1) + ":K" + (Row+1) +")");
		
		Row=Row+2;
	
		if (Mrk.compareTo("NYC")==0)
		{
			this.getCell(5,Row).setCellFormula("SUM(F18,F22)");
			this.getCell(6,Row).setCellFormula("SUM(G18,G22)");
			this.getCell(7,Row).setCellFormula("SUM(H18,H22)");
			this.getCell(8,Row).setCellFormula("SUM(I18,I22)");
			this.getCell(9,Row).setCellFormula("SUM(J18,J22)");
		}
		if (Mrk.compareTo("LIFFE")==0)
		{
			this.getCell(5,Row).setCellFormula("SUM(F39,F43)");
			this.getCell(6,Row).setCellFormula("SUM(G39,G43)");
			this.getCell(7,Row).setCellFormula("SUM(H39,H43)");
			this.getCell(8,Row).setCellFormula("SUM(I39,I43)");
			this.getCell(9,Row).setCellFormula("SUM(J39,J43)");
			this.getCell(10,Row).setCellFormula("SUM(K39,K43)");
		}
		
		this.getCell(11,Row).setCellFormula("SUM(F" + (Row+1) + ":K" + (Row+1) +")");
		
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}	
		
	  }
	}